-- 创建一个包含主键约束的表
CREATE TABLE employees (
    employee_id NUMBER(10) CONSTRAINT emp_pk PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    phone_number VARCHAR2(15)
);
 
 
create table LOCATIONS(
	location_id NUMBER PRIMARY KEY not null,
	LOCATION_TEXT VARCHAR2(1000)
);
 
-- 创建一个包含外键约束的表
CREATE TABLE departments (
    department_id NUMBER(10) CONSTRAINT dept_pk PRIMARY KEY,
    department_name VARCHAR2(50),
    location_id NUMBER(10),
    CONSTRAINT dept_fk FOREIGN KEY (location_id) REFERENCES locations(location_id)
);
 
-- 创建一个包含唯一约束的表
CREATE TABLE user_accounts (
    username VARCHAR2(50) CONSTRAINT user_acc_pk PRIMARY KEY,
    password VARCHAR2(50),
    email VARCHAR2(100) CONSTRAINT user_email_uk UNIQUE
);
 
-- 创建一个包含检查约束的表
CREATE TABLE orders (
    order_id NUMBER(10) CONSTRAINT order_pk PRIMARY KEY,
    order_date DATE CONSTRAINT order_date_ck CHECK (order_date > TO_DATE('2023-01-01', 'YYYY-MM-DD')),
    total_amount NUMBER(10, 2) CONSTRAINT order_total_ck CHECK (total_amount > 0)
);
 
-- 创建一个包含非空约束的表
CREATE TABLE products (
    product_id NUMBER(10) CONSTRAINT prod_pk PRIMARY KEY,
    product_name VARCHAR2(100) CONSTRAINT prod_name_nn NOT NULL,
    price NUMBER(10, 2) CONSTRAINT prod_price_nn NOT NULL
);


select OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from all_constraints where (owner = 'LBK' and TABLE_NAME = 'ORDERS') or (owner = 'LBK' and TABLE_NAME = 'PRODUCTS');

select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where owner = 'LBK' and TABLE_NAME in ( 'ORDERS','PRODUCTS');

select OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from all_constraints where (owner = 'LBK' and TABLE_NAME = 'ORDERS') or (owner = 'LBK' and TABLE_NAME = 'PRODUCTS');

select OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where owner = 'LBK';

Elapsed: 00:00:00.40
13:54:06 SQL> select OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where owner = 'LBK';

OWNER                                                        TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------------------------------------ ------------------------------ ------------------------------ -
LBK                                                          PRODUCTS                       PROD_PRICE_NN                  C
LBK                                                          PRODUCTS                       PROD_NAME_NN                   C
LBK                                                          ORDERS                         ORDER_TOTAL_CK                 C
LBK                                                          ORDERS                         ORDER_DATE_CK                  C
LBK                                                          DEPARTMENTS                    DEPT_FK                        R
LBK                                                          EMPLOYEES                      EMP_PK                         P
LBK                                                          USER_ACCOUNTS                  USER_ACC_PK                    P
LBK                                                          USER_ACCOUNTS                  USER_EMAIL_UK                  U
LBK                                                          ORDERS                         ORDER_PK                       P
LBK                                                          PRODUCTS                       PROD_PK                        P
LBK                                                          LOCATIONS                      SYS_C0011097                   P

OWNER                                                        TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------------------------------------ ------------------------------ ------------------------------ -
LBK                                                          DEPARTMENTS                    DEPT_PK                        P

12 rows selected.

Elapsed: 00:00:00.00
13:58:49 SQL> select TABLE_OWNER,TABLE_NAME,INDEX_NAME from dba_indexes where (TABLE_OWNER = 'LBK' and TABLE_NAME = 'ORDERS') or (TABLE_OWNER = 'LBK' and TABLE_NAME = 'PRODUCTS') and UNIQUENESS = 'UNIQUE' and STATUS = 'VALID';


TABLE_OWNER                    TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------ ------------------------------
LBK                            PRODUCTS                       PROD_PK
LBK                            ORDERS                         ORDER_PK

Elapsed: 00:00:00.09

select TABLE_OWNER,TABLE_NAME,INDEX_NAME from dba_indexes where TABLE_OWNER = 'LBK' and UNIQUENESS = 'UNIQUE' and STATUS = 'VALID';

14:03:22 SQL> select OWNER,TABLE_OWNER,TABLE_NAME,INDEX_NAME from dba_indexes where TABLE_OWNER = 'LBK1' and UNIQUENESS = 'UNIQUE' and STATUS = 'VALID';

TABLE_OWNER                    TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------ ------------------------------
LBK                            DEPARTMENTS                    DEPT_PK
LBK                            EMPLOYEES                      EMP_PK
LBK                            EMPLOYEES                      IDX_XXXXXXXXXXXXX
LBK                            LOCATIONS                      SYS_C0011097
LBK                            ORDERS                         ORDER_PK
LBK                            PRODUCTS                       PROD_PK
LBK                            USER_ACCOUNTS                  USER_ACC_PK
LBK                            USER_ACCOUNTS                  USER_EMAIL_UK

8 rows selected.

14:03:50 SQL> select OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where owner = 'LBK';

OWNER                                                        TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------------------------------------ ------------------------------ ------------------------------ -
LBK                                                          DEPARTMENTS                    DEPT_PK                        P
LBK                                                          DEPARTMENTS                    DEPT_FK                        R
LBK                                                          EMPLOYEES                      EMP_PK                         P
LBK                                                          LOCATIONS                      SYS_C0011097                   P
LBK                                                          ORDERS                         ORDER_PK                       P
LBK                                                          ORDERS                         ORDER_DATE_CK                  C
LBK                                                          ORDERS                         ORDER_TOTAL_CK                 C
LBK                                                          PRODUCTS                       PROD_PK                        P
LBK                                                          PRODUCTS                       PROD_NAME_NN                   C
LBK                                                          PRODUCTS                       PROD_PRICE_NN                  C
LBK                                                          USER_ACCOUNTS                  USER_EMAIL_UK                  U

OWNER                                                        TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------------------------------------ ------------------------------ ------------------------------ -
LBK                                                          USER_ACCOUNTS                  USER_ACC_PK                    P

12 rows selected.

Elapsed: 00:00:00.38


Elapsed: 00:00:00.00
14:05:05 SQL> select OWNER,TABLE_OWNER,TABLE_NAME,INDEX_NAME from dba_indexes where TABLE_OWNER = 'LBK' and UNIQUENESS = 'UNIQUE' and STATUS = 'VALID';

OWNER                          TABLE_OWNER                    TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
LBK                            LBK                            DEPARTMENTS                    DEPT_PK
LBK                            LBK                            EMPLOYEES                      EMP_PK
SYS                            LBK                            EMPLOYEES                      IDX_XXXXXXXXXXXXX
LBK                            LBK                            LOCATIONS                      SYS_C0011097
LBK                            LBK                            ORDERS                         ORDER_PK
LBK                            LBK                            PRODUCTS                       PROD_PK
LBK                            LBK                            USER_ACCOUNTS                  USER_ACC_PK
LBK                            LBK                            USER_ACCOUNTS                  USER_EMAIL_UK

8 rows selected.


--------- TABLE:UK ------------
select * from (select TABLE_OWNER, TABLE_NAME, INDEX_NAME from dba_indexes where TABLE_OWNER = 'LBK' and UNIQUENESS = 'UNIQUE' and STATUS = 'VALID') t
minus
select OWNER, TABLE_NAME, CONSTRAINT_NAME from dba_constraints where owner = 'LBK' and CONSTRAINT_TYPE = 'P';

--------- TABLE:PK ------------ PRIMARY_KEY
select OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where owner = 'LBK' and CONSTRAINT_TYPE = 'P';

--------- TABLE:FK ------------ 
select OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where owner = 'LBK' and CONSTRAINT_TYPE = 'R';


create or replace trigger lbk.trigger_test01
after insert on lbk.EMPLOYEES
for each row
begin
insert into lbk.LOCATIONS values (:new.EMPLOYEE_ID, :new.EMPLOYEE_ID);
end;
/

create or replace trigger lbk.trigger_test02
after insert on lbk.EMPLOYEES
for each row
begin
insert into LOCATIONS values (:new.EMPLOYEE_ID, :new.EMPLOYEE_ID);
end;
/

create or replace trigger lbk.trigger_test03
after insert on lbk.employees
for each row
begin
insert    into   LBK.locations values (:new.EMPLOYEE_ID, :new.EMPLOYEE_ID);
end;
/


14:45:24 SQL> select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_OWNER,TABLE_NAME,STATUS from dba_triggers where TABLE_OWNER = 'LBK';

OWNER                          TRIGGER_NAME                   TRIGGER_TYPE     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ---------------- ------------------------------ ------------------------------ --------
LBK                            TRIGGER_TEST01                 AFTER EACH ROW   LBK                            EMPLOYEES                      ENABLED

Elapsed: 00:00:00.07
14:45:54 SQL> 

--------- TABLE:TRIGGER ------------ 
select TABLE_OWNER, TABLE_NAME, TRIGGER_NAME from dba_triggers where TABLE_OWNER = 'LBK' and STATUS = 'ENABLED';

--------- TABLE:TRIGGER:TRIGGER_BODY ------------ 
select TABLE_OWNER, TABLE_NAME, TRIGGER_NAME, TRIGGER_BODY from dba_triggers where TABLE_OWNER = 'LBK' and STATUS = 'ENABLED';



create table lbk.test1 (id int) compress;
create GLOBAL TEMPORARY table lbk.test2(id int);
create table lbk.test3(id int primary key not null, name varchar(10)) ORGANIZATION INDEX;
CREATE TABLE lbk.sales (
    sale_id NUMBER,
    product_id NUMBER,
    sale_date DATE
) PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
    PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2023', 'DD-MON-YYYY')),
    PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'))
);

CREATE TABLE lbk.sales2 (
    sale_id NUMBER,
    product_id NUMBER,
    sale_date DATE
) compress PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
    PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2023', 'DD-MON-YYYY')),
    PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'))
) ;


! echo 1,1,1 > /tmp/employees.csv
CREATE DIRECTORY ext_dir AS '/tmp';
 
-- 2. 创建外部表
CREATE TABLE lbk.ext_employees (
  employee_id NUMBER(6),
  employee_name VARCHAR2(50),
  department_id NUMBER(4)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' -- CSV格式
  )
  LOCATION ('employees.csv') -- 文件名
);


15:53:24 SQL>  select OWNER, TABLE_NAME, IOT_TYPE, TEMPORARY, PARTITIONED, COMPRESSION from dba_tables where owner = 'LBK';

OWNER                          TABLE_NAME                     IOT_TYPE     T PAR COMPRESS
------------------------------ ------------------------------ ------------ - --- --------
LBK                            EMPLOYEES                                   N NO  DISABLED
LBK                            LOCATIONS                                   N NO  DISABLED
LBK                            TEST2                                       Y NO  DISABLED
LBK                            SALES2                                      N YES
LBK                            TEST3                          IOT          N NO  DISABLED
LBK                            SALES                                       N YES
LBK                            TEST1                                       N NO  ENABLED
LBK                            DEPARTMENTS                                 N NO  DISABLED
LBK                            PRODUCTS                                    N NO  DISABLED
LBK                            ORDERS                                      N NO  DISABLED
LBK                            USER_ACCOUNTS                               N NO  DISABLED

11 rows selected.
Elapsed: 00:00:00.12

分区表：COMPRESS：为空 
IOT_TYPE:

15:56:27 SQL> select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, COMPRESSION, INTERVAL from dba_tab_partitions where TABLE_OWNER = 'LBK' and COMPRESSION = 'ENABLED';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME                 COMPRESS INT
------------------------------ ------------------------------ ------------------------------ -------- ---
LBK                            SALES2                         SALES_Q1                       ENABLED  NO
LBK                            SALES2                         SALES_Q2                       ENABLED  NO
LBK                            SALES2                         SALES_Q3                       ENABLED  NO
LBK                            SALES2                         SALES_Q4                       ENABLED  NO

8 rows selected.




--------- TABLE:ATTR ------------
select OWNER, TABLE_NAME, IOT_TYPE, TEMPORARY, PARTITIONED, COMPRESSION from dba_tables where owner = 'LBK';

--------- TABLE:ATTR:COMPRESSION ------------
select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, COMPRESSION from dba_tab_partitions where TABLE_OWNER = 'LBK' and COMPRESS = 'ENABLED';

--------- TABLE:ATTR:EXTERNAL ------------
select OWNER, TABLE_NAME from dba_external_tables where OWNER = 'LBK';


create table lbk.test11 (id int primary key not null, text XMLType);
CREATE TABLE lbk.test12 (
    file_id NUMBER PRIMARY KEY,
    file_name VARCHAR2(100),
    file_data BFILE
);

create table lbk.test13 (id int primary key not null, text TIMESTAMP WITH LOCAL TIME ZONE);




O2K_Support_Type=VARCHAR2,NVARCHAR2,NUMBER,LONG,DATE,RAW,LONG RAW,ROWID,CHAR,NCHAR,BINARY_FLOAT,BINARY_DOUBLE,CLOB,NCLOB,BLOB,TIMESTAMP,UROWID
O2O_Support_Type=VARCHAR2,NVARCHAR2,NUMBER,LONG,DATE,RAW,LONG RAW,ROWID,CHAR,NCHAR,BINARY_FLOAT,BINARY_DOUBLE,CLOB,NCLOB,BLOB,TIMESTAMP,UROWID


CREATE TABLE lbk.EMP3 (
EMPNO NUMBER(6),
SAL NUMBER(8,2),
COMM NUMBER(8,2),
SAL_PACK GENERATED ALWAYS AS (SAL + NVL(COMM, 0)) VIRTUAL
);



CREATE TABLE interval_number_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20)
)
PARTITION BY RANGE(employee_id)
INTERVAL (10)
(
  PARTITION partition10 VALUES LESS THAN(10)
);

insert into INTERVAL_NUMBER_TABLE01 values (201209, 'name09');
insert into INTERVAL_NUMBER_TABLE01 values (201210, 'name10');
insert into INTERVAL_NUMBER_TABLE01 values (201211, 'name11');
insert into INTERVAL_NUMBER_TABLE01 values (201212, 'name12');
insert into INTERVAL_NUMBER_TABLE01 values (201301, 'name01');
insert into INTERVAL_NUMBER_TABLE01 values (201402, 'name02');
insert into INTERVAL_NUMBER_TABLE01 values (201503, 'name03');

--------- TABLE:COLUMN:DATA_TYPE:NON-SUPPORT ---------
select OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID, VIRTUAL_COLUMN from dba_tab_cols where OWNER = 'LBK' and (VIRTUAL_COLUMN = 'YES' or (DATA_TYPE not in ('VARCHAR2','NVARCHAR2','NUMBER','LONG','DATE','RAW','LONG RAW','ROWID','CHAR','NCHAR','BINARY_FLOAT','BINARY_DOUBLE','CLOB','NCLOB','BLOB','TIMESTAMP','UROWID') and substr(DATA_TYPE,1, 9) != 'TIMESTAMP') and COLUMN_NAME != 'SYS_NC00003$');

SQL> select OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID, VIRTUAL_COLUMN from dba_tab_cols where OWNER = 'LBK' and (VIRTUAL_COLUMN = 'YES' or (DATA_TYPE not in ('VARCHAR2','NVARCHAR2','NUMBER','LONG','DATE','RAW','LONG RAW','ROWID','CHAR','NCHAR','BINARY_FLOAT','BINARY_DOUBLE','CLOB','NCLOB','BLOB','TIMESTAMP','UROWID') and substr(DATA_TYPE,1, 9) != 'TIMESTAMP') and COLUMN_NAME != 'SYS_NC00003$');

OWNER                          TABLE_NAME                     COLUMN_NAME                    DATA_TYPE                                 COLUMN_ID VIR
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ---------- ---
LBK                            TEST12                         FILE_DATA                      BFILE                                             3 NO
LBK                            TEST11                         TEXT                           XMLTYPE                                           2 YES
LBK                            EMP3                           SAL_PACK                       NUMBER                                            4 YES

SQL> 
--------- TABLE:COLUMN:DATA_TYPE:NON-SUPPORT:VIRTUAL_COLUMN ---------


--------- DATABASE:SUPPLEMENTAL_LOG ---------
select NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;

SQL> select NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;

NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    FOR SUPPLEME SUP SUP SUP SUP
--------- ------------ -------------------- ---------------- --- -------- --- --- --- ---
ORCL      ARCHIVELOG   READ WRITE           PRIMARY          NO  YES      YES NO  NO  NO


--------- TABLE:SUPPLEMENTAL_LOG ---------
SELECT OWNER, TABLE_NAME, LOG_GROUP_TYPE FROM ALL_LOG_GROUPS WHERE OWNER = 'LBK';


--------- DATABASE:PARAMETER ---------
--filesystemio_options=none
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME IN ('filesystemio_options');


--------- USER:GRANTS ---------
SELECT GRANTEE,PRIVILEGE from dba_sys_privs WHERE GRANTEE = 'LBK';
SELECT GRANTEE,GRANTED_ROLE FROM dba_role_privs WHERE GRANTEE = 'LBK';
SELECT GRANTEE,PRIVILEGE,TABLE_NAME FROM dba_tab_privs WHERE GRANTEE = 'LBK';




grant execute on sys.dbms_lock to LBK;
grant execute on dbms_flashback to LBK;
grant select any table, select any dictionary,alter system,exp_full_database, connect to LBK;
grant connect,resource,create table to LBK;
grant unlimited tablespace to LBK;

grant select,flashback on sys.user$ to LBK;
grant select,flashback on sys.ts$ to LBK;
grant select,flashback on sys.tab$ to LBK;
grant select,flashback on sys.dba_objects to LBK;
grant select,flashback on sys.obj$ to LBK;
grant select,flashback on sys.seg$ to LBK;
grant select,flashback on sys.deferred_stg$ to LBK;
grant select,flashback on sys.tabpart$ to LBK;
grant select,flashback on sys.tabsubpart$ to LBK;
grant select,flashback on sys.tabcompart$ to LBK;
grant select,flashback on sys.col$ to LBK;
grant select,flashback on sys.partobj$ to LBK;
grant select,flashback on sys.partcol$ to LBK;
grant select,flashback on sys.subpartcol$ to LBK;
grant select,flashback on sys.clu$ to LBK;


create or replace view DBPS_XKCCLE as select * from sys.x$kccle;
create or replace view DBPS_XKCCLH as select * from sys.x$kcclh;
create or replace view DBPS_XKCCCF as select * from sys.x$kcccf;
create or replace view DBPS_XKCCCP as select * from sys.x$kcccp;
create or replace view DBPS_XKCCDI as select * from sys.x$kccdi;
create or replace view DBPS_XKCRMF as select * from sys.x$kcrmf;
create or replace view DBPS_XKTUXE as select * from sys.x$ktuxe;
create or replace view DBPS_XLE as select * from sys.x$le;
create or replace view DBPS_XBH as select * from sys.x$bh;



--------- VIEW ---------
select OWNER,VIEW_NAME from dba_views where OWNER = 'SYS' AND VIEW_NAME IN ('DBPS_XKCCLE', 'DBPS_XKCCLH', 'DBPS_XKCCCF', 'DBPS_XKCCCP', 'DBPS_XKCCDI', 'DBPS_XKCRMF', 'DBPS_XKTUXE', 'DBPS_XLE', 'DBPS_XBH');

OWNER                          VIEW_NAME
------------------------------ ------------------------------
SYS                            DBPS_XBH
SYS                            DBPS_XKCCCF
SYS                            DBPS_XKCCCP
SYS                            DBPS_XKCCDI
SYS                            DBPS_XKCCLE
SYS                            DBPS_XKCCLH
SYS                            DBPS_XKCRMF
SYS                            DBPS_XKTUXE
SYS                            DBPS_XLE

9 rows selected.



select * from (
select a.OWNER, a.TABLE_NAME, nvl2(a.IOT_TYPE, 'Y', 'N') as IOT, a.TEMPORARY, decode(a.PARTITIONED, 'YES', 'Y', 'N') as PARTITIONED, decode(nvl(a.COMPRESSION, b.DEF_COMPRESSION), 'ENABLED', 'Y', 'N') as COMPRESSION, nvl2(b.INTERVAL, 'Y', 'N') as INTERVAL, nvl2(c.table_name, 'Y', 'N') as external from dba_tables a 
left join dba_part_tables b on a.owner = b.owner and a.table_name = b.table_name  
left join dba_external_tables c on a.owner = c.owner and a.table_name = c.table_name  
) t where t.owner = 'LBK' and rownum < 20;


select * from (
select a.OWNER, a.TABLE_NAME, nvl2(a.IOT_TYPE, 'Y', 'N') as IOT, a.TEMPORARY, decode(a.PARTITIONED, 'YES', 'Y', 'N') as PARTITIONED, decode(nvl(a.COMPRESSION, b.DEF_COMPRESSION), 'ENABLED', 'Y', 'N') as COMPRESSION, nvl2(b.INTERVAL, 'Y', 'N') as INTERVAL, nvl2(c.table_name, 'Y', 'N') as external, nvl2(f.CONSTRAINT_NAME, 'Y', 'N') as PK, d.LOG_GROUP_TYPE, decode(e.INDEX_NAME, f.CONSTRAINT_NAME, '', e.INDEX_NAME) UK, g.CONSTRAINT_NAME FK, h.TRIGGER_NAME from dba_tables a 
left join dba_part_tables b on a.owner = b.owner and a.table_name = b.table_name   
left join dba_external_tables c on a.owner = c.owner and a.table_name = c.table_name  
left join (SELECT OWNER, TABLE_NAME, LISTAGG(decode(LOG_GROUP_TYPE, 'ALL COLUMN LOGGING', 'ALL', 'FOREIGN KEY LOGGING','FK','PRIMARY KEY LOGGING','PK','UNIQUE KEY LOGGING','UI','USER LOG GROUP','N'), ',') WITHIN GROUP (ORDER BY LOG_GROUP_TYPE) AS LOG_GROUP_TYPE FROM ALL_LOG_GROUPS GROUP BY OWNER, TABLE_NAME) d on a.owner = d.owner and a.table_name = d.table_name
left join (select TABLE_OWNER as OWNER,TABLE_NAME, LISTAGG(INDEX_NAME, ',') WITHIN GROUP (ORDER BY INDEX_NAME) AS INDEX_NAME from dba_indexes where UNIQUENESS = 'UNIQUE' and STATUS = 'VALID' GROUP BY TABLE_OWNER, TABLE_NAME) e on a.owner = e.owner and a.table_name = e.table_name
left join dba_constraints f on a.owner = f.owner and a.table_name = f.table_name and f.CONSTRAINT_TYPE = 'P'
left join (select OWNER,TABLE_NAME, LISTAGG(CONSTRAINT_NAME, ',') WITHIN GROUP (ORDER BY CONSTRAINT_NAME) AS CONSTRAINT_NAME from dba_constraints where CONSTRAINT_TYPE = 'R' GROUP BY OWNER, TABLE_NAME) g on a.owner = g.owner and a.table_name = g.table_name
left join (select TABLE_OWNER as OWNER, TABLE_NAME, LISTAGG(TRIGGER_NAME, ',') WITHIN GROUP (ORDER BY TRIGGER_NAME) AS TRIGGER_NAME from dba_triggers where STATUS = 'ENABLED' GROUP BY TABLE_OWNER, TABLE_NAME) h on a.owner = h.owner and a.table_name = h.table_name   
) t where t.owner = 'DSG' and TABLE_NAME = 'EMPLOYEES' ;


select * from (
select a.OWNER, a.TABLE_NAME, nvl2(a.IOT_TYPE, 'Y', 'N') as IOT, a.TEMPORARY, decode(a.PARTITIONED, 'YES', 'Y', 'N') as PARTITIONED, decode(nvl(a.COMPRESSION, b.DEF_COMPRESSION), 'ENABLED', 'Y', 'N') as COMPRESSION, nvl2(b.INTERVAL, 'Y', 'N') as INTERVAL, nvl2(c.table_name, 'Y', 'N') as external, nvl2(f.CONSTRAINT_NAME, 'Y', 'N') as PK, d.LOG_GROUP_TYPE, decode(e.INDEX_NAME, f.CONSTRAINT_NAME, '', e.INDEX_NAME) UK, g.CONSTRAINT_NAME FK, h.TRIGGER_NAME from
(select OWNER, TABLE_NAME, IOT_TYPE, TEMPORARY, PARTITIONED,COMPRESSION from dba_tables where owner = 'LBK1' and rownum < 20) a 
left join dba_part_tables b on a.owner = b.owner and a.table_name = b.table_name   
left join dba_external_tables c on a.owner = c.owner and a.table_name = c.table_name  
left join (SELECT OWNER, TABLE_NAME, LISTAGG(decode(LOG_GROUP_TYPE, 'ALL COLUMN LOGGING', 'ALL', 'FOREIGN KEY LOGGING','FK','PRIMARY KEY LOGGING','PK','UNIQUE KEY LOGGING','UI','USER LOG GROUP','N'), ',') WITHIN GROUP (ORDER BY LOG_GROUP_TYPE) AS LOG_GROUP_TYPE FROM ALL_LOG_GROUPS GROUP BY OWNER, TABLE_NAME) d on a.owner = d.owner and a.table_name = d.table_name
left join (select TABLE_OWNER as OWNER,TABLE_NAME, LISTAGG(INDEX_NAME, ',') WITHIN GROUP (ORDER BY INDEX_NAME) AS INDEX_NAME from dba_indexes where UNIQUENESS = 'UNIQUE' and STATUS = 'VALID' GROUP BY TABLE_OWNER, TABLE_NAME) e on a.owner = e.owner and a.table_name = e.table_name
left join dba_constraints f on a.owner = f.owner and a.table_name = f.table_name and f.CONSTRAINT_TYPE = 'P'
left join (select OWNER,TABLE_NAME, LISTAGG(CONSTRAINT_NAME, ',') WITHIN GROUP (ORDER BY CONSTRAINT_NAME) AS CONSTRAINT_NAME from dba_constraints where CONSTRAINT_TYPE = 'R' GROUP BY OWNER, TABLE_NAME) g on a.owner = g.owner and a.table_name = g.table_name
left join (select TABLE_OWNER as OWNER, TABLE_NAME, LISTAGG(TRIGGER_NAME, ',') WITHIN GROUP (ORDER BY TRIGGER_NAME) AS TRIGGER_NAME from dba_triggers where STATUS = 'ENABLED' GROUP BY TABLE_OWNER, TABLE_NAME) h on a.owner = h.owner and a.table_name = h.table_name   
) t;



with T0 as (SELECT * FROM (SELECT a.*, ROWNUM rn FROM (select OWNER,TABLE_NAME,IOT_TYPE,TEMPORARY,PARTITIONED,COMPRESSION from dba_tables where OWNER = 'LBK' order by TABLE_NAME) a WHERE ROWNUM <= 97900) WHERE rn >= 97851)
select * from (select a.OWNER, a.TABLE_NAME, nvl2(a.IOT_TYPE, 'Y', 'N') as IOT, a.TEMPORARY, decode(a.PARTITIONED, 'YES', 'Y', 'N') as PARTITIONED, decode(nvl(a.COMPRESSION, b.DEF_COMPRESSION), 'ENABLED', 'Y', 'N') as COMPRESSION, nvl2(b.INTERVAL, 'Y', 'N') as PARTITION_INTERVAL, nvl2(c.table_name, 'Y', 'N') as external, f.CONSTRAINT_NAME as PK, d.LOG_GROUP_TYPE SUPPLEMENTAL_LOG, e.INDEX_NAME UK, g.CONSTRAINT_NAME FK, h.TRIGGER_NAME TRIGGERS from T0 a
left join dba_part_tables b on a.owner = b.owner and a.table_name = b.table_name
left join dba_external_tables c on a.owner = c.owner and a.table_name = c.table_name
left join (SELECT OWNER, TABLE_NAME, LISTAGG(decode(LOG_GROUP_TYPE, 'ALL COLUMN LOGGING', 'ALL', 'FOREIGN KEY LOGGING','FK','PRIMARY KEY LOGGING','PK','UNIQUE KEY LOGGING','UI','USER LOG GROUP','N'), ',') WITHIN GROUP (ORDER BY LOG_GROUP_TYPE) AS LOG_GROUP_TYPE FROM ALL_LOG_GROUPS GROUP BY OWNER, TABLE_NAME) d on a.owner = d.owner and a.table_name = d.table_name
left join (select TABLE_OWNER as OWNER,TABLE_NAME, LISTAGG(INDEX_NAME, ',') WITHIN GROUP (ORDER BY INDEX_NAME) AS INDEX_NAME from dba_indexes where UNIQUENESS = 'UNIQUE' and STATUS = 'VALID' GROUP BY TABLE_OWNER, TABLE_NAME) e on a.owner = e.owner and a.table_name = e.table_name
left join dba_constraints f on a.owner = f.owner and a.table_name = f.table_name and f.CONSTRAINT_TYPE = 'P'
left join (select OWNER,TABLE_NAME, LISTAGG(CONSTRAINT_NAME, ',') WITHIN GROUP (ORDER BY CONSTRAINT_NAME) AS CONSTRAINT_NAME from dba_constraints where CONSTRAINT_TYPE = 'R' GROUP BY OWNER, 
TABLE_NAME) g on a.owner = g.owner and a.table_name = g.table_name
left join (select TABLE_OWNER as OWNER, TABLE_NAME, LISTAGG(TRIGGER_NAME, ',') WITHIN GROUP (ORDER BY TRIGGER_NAME) AS TRIGGER_NAME from dba_triggers where STATUS = 'ENABLED' GROUP BY TABLE_OWNER, TABLE_NAME) h on a.owner = h.owner and a.table_name = h.table_name
) t order by t.TABLE_NAME;



create table t_test(
id  integer  generated  ALWAYS as identity,
transid  varchar2(32) not null);

表已创建。

SQL> select OWNER,TABLE_NAME,COLUMN_NAME,NULLABLE,IDENTITY_COLUMN from dba_tab_cols where TABLE_NAME = 'T_TEST';

OWNER                          TABLE_NAME                               COLUMN_NAME                    N IDE
------------------------------ ---------------------------------------- ------------------------------ - ---
SYS                            T_TEST                                   ID                             N YES
SYS                            T_TEST                                   TRANSID                        N NO
SQL> insert into t_test(id,transid) values(1,'20220101');
insert into t_test(id,transid) values(1,'20220101')
                   *
第 1 行出现错误:
ORA-32795: 无法插入到“始终生成”身份列

select
a.TABLE_OWNER OWNER,
a.TABLE_NAME,
a.INDEX_NAME
from
(select
TABLE_OWNER,
TABLE_NAME,
INDEX_NAME,
COLUMN_NAME
from
dba_ind_columns
where
TABLE_OWNER = 'LBK'
and INDEX_NAME in ('SYS_C00213913', 'TEST_UI', 'UK_TEST1')) a
left join dba_tab_cols b on a.TABLE_OWNER = b.OWNER
and a.TABLE_NAME = b.TABLE_NAME
and a.COLUMN_NAME = b.COLUMN_NAME
where b.NULLABLE = 'Y';

    select TABLE_NAME, COLUMN_NAME, NULLABLE from dba_tab_cols where table_name = 'TEST_UK'